package sc.tool.sqlgen.branchinfo.pfs;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import sc.prototype.common.ConsoleUtil;
import sc.prototype.common.Utils;
import sc.tool.sqlgen.branchinfo.DBUtil;
import sc.tool.sqlgen.branchinfo.FileUtil;
import sc.tool.sqlgen.branchinfo.XlsxUtil;

public class BranchInfoUpdateConfirmSql implements ConsoleUtil {

    String filePath = "D:/WeibopayMemo/出款服务FOS/branh_info_update_1227.xlsx";
    String sheetName = "更新分支行";
    int startRowIndex = 1;
    int endRowIndex = 100000000;

    SimpleStatistic statistic = new SimpleStatistic();
    DBUtil dbUtil = new DBUtil();

    public List<String[]> loadUpdate() {
        Console.info("加载文件：{}", filePath);
        Console.info("加载页：{}", sheetName);
        Console.info("加载行：[{}, {})", startRowIndex, endRowIndex);
        List<String[]> rows = XlsxUtil.loadItems(filePath, sheetName, startRowIndex, endRowIndex);
        Console.info("加载实际行数：{}", rows.size());
        return rows;
    }

    public static final String FOS_CONFIRM_TEMPLATE = "select * from fos.tm_branch_info bi where bi.branch_name = '%s' and bi.branch_id = '%s'";

    public void confirmFOS() {
        List<String[]> rows = loadUpdate();
        int count = 1;
        for (Iterator<String[]> iter = rows.iterator(); iter.hasNext();) {
            String[] row = iter.next();
            boolean print = ((count - 1) % 100 == 0);
            if (print) {
                Console.info("确认数据存在-{}：{}", count, row);
            }
            String sql = String.format(FOS_CONFIRM_TEMPLATE, row[2], row[0]);
            if (print) {
                Console.info("确认SQL-{}：{}", count, sql);
            }
            boolean exist = dbUtil.exist(sql, "branch_id");
            if (!exist) {
                Console.info("数据不存在！{}", Utils.toString(row));
                statistic.addStatistic("数据不存在");
            } else {
                statistic.addStatistic("数据存在");
            }
            count++;
        }
        statistic.printStatistic();
    }

    public static final String MEMBER_PERSON_TEMPLATE = "select * from member.tr_person_bank_account pa where pa.bank_branch in (";
    public static final String MEMBER_COMPANY_TEMPLATE = "select * from member.tr_company_bank_account pa where pa.bank_branch in (";

    String memberOutputFilePath = "D:/WeibopayMemo/出款服务FOS/member_confirm_gen.sql";

    public void genMASql(String template) {
        List<String[]> rows = loadUpdate();
        int count = 1;
        List<String> branchNames = new ArrayList<String>();
        for (Iterator<String[]> iter = rows.iterator(); iter.hasNext();) {
            String[] row = iter.next();
            branchNames.add(row[2]);
            if (count % 1000 == 0) {
                genSql(template, branchNames);
            }
            statistic.addStatistic("处理数据总数");
            count++;
        }
        if (branchNames.size() > 0) {
            genSql(template, branchNames);
        }
        statistic.printStatistic();
        //select * from member.tr_person_bank_account pa where pa.bank_branch in ('', '')
    }

    public void genSql(String template, List<String> branchNames) {
        StringBuilder sb = new StringBuilder(template);
        for (String branchName : branchNames) {
            sb.append("'").append(branchName).append("', ");
        }
        sb.setLength(sb.length() - 2);
        sb.append(");\r\n");
        FileUtil.writeFile(Arrays.asList(sb.toString()), memberOutputFilePath);
        branchNames.clear();
        statistic.addStatistic("生成SQL总数");
    }

    public static void main(String[] args) {
        BranchInfoUpdateConfirmSql sql = new BranchInfoUpdateConfirmSql();
        //校验通过，FOS更新全部存在
        //        sql.startRowIndex = 3848;
        //        sql.endRowIndex = 3849;
        //        sql.confirmFOS();

        //
//        sql.genMASql(MEMBER_PERSON_TEMPLATE);

        sql.genMASql(MEMBER_COMPANY_TEMPLATE);

    }
}
